﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;

namespace KKCai.HNKL.Test
{
    class ConvertDB
    {
        public DataTable GetData()
        {
            DataTable dt = new DataTable();

            string str = @"Data Source=F:\sqlite\HNKL_20130113.db3;Version=3;";
            string cmd = "select * from record";

            using (SQLiteConnection con = new SQLiteConnection(str))
            {
                using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd, con))
                {
                    da.Fill(dt);
                }
            }

            return dt;
        }

        public void InitData()
        {
            DataTable dt = this.GetData();

            string str = @"Server=.\SQLEXPRESS;DataBase=HNKL;uid=sa;pwd=;";
            string sql = "insert into _hnkl values('{0}','{1}','{2}','{3}','{4}','{5}')";

            using (SqlConnection con = new SqlConnection(str))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    con.Open();
                    cmd.Transaction = con.BeginTransaction();
                    
                    foreach (DataRow item in dt.Rows)
                    {
                        cmd.CommandText = String.Format(sql, item[1], item[2], item[3], item[4], item[5], item[6]);
                        cmd.ExecuteNonQuery();
                    }

                    cmd.Transaction.Commit();
                    
                }
            }

        }

        public void InitDataSQLite()
        {
            DataTable dt = this.GetData();

            string str = @"Data Source=F:\sqlite\HNKL.db3;Version=3;";
            string sql = "insert into hnkl(phase,result,asc_result,a1,a3,p3) values('{0}','{1}','{2}','{3}','{4}','{5}')";

            using (SQLiteConnection con = new SQLiteConnection(str))
            {
                using (SQLiteCommand cmd = con.CreateCommand())
                {
                    con.Open();
                    cmd.Transaction = con.BeginTransaction();

                    foreach (DataRow item in dt.Rows)
                    {
                        cmd.CommandText = String.Format(sql, item[1], item[2], item[3], item[4], item[5], item[6]);
                        cmd.ExecuteNonQuery();
                    }

                    cmd.Transaction.Commit();

                }
            }

        }

        public DataTable GetData2()
        {
            DataTable dt = new DataTable();

            string str = @"Data Source=F:\sqlite\HNKL10.db3;Version=3;";
            string cmd = "select phase,result from record";

            using (SQLiteConnection con = new SQLiteConnection(str))
            {
                using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd, con))
                {
                    da.Fill(dt);
                }
            }

            return dt;
        }

        public void InitData2()
        {
            DataTable dt = this.GetData2();

            string str = @"Server=.\SQLEXPRESS;DataBase=HNKL;uid=sa;pwd=;";
            string sql = "insert into _hnkl values('{0}','{1}','{2}','{3}','{4}','{5}')";

            using (SqlConnection con = new SqlConnection(str))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    con.Open();
                    cmd.Transaction = con.BeginTransaction();

                    string result, asc, a1, a3, p3;
                    foreach (DataRow item in dt.Rows)
                    {
                        result = Convert.ToString(item[1]);
                        asc = Utils.Sort(result);
                        a1 = result.Substring(0, 2);
                        a3 = result.Substring(0, 8);
                        p3 = Utils.Sort(a3);

                        cmd.CommandText = String.Format(sql, item[0], result, asc, a1, a3, p3);
                        cmd.ExecuteNonQuery();
                    }

                    cmd.Transaction.Commit();

                }
            }

        }

        public void InitData2SQLite()
        {
            DataTable dt = this.GetData2();

            string str = @"Data Source=F:\sqlite\HNKL.db3;Version=3;";
            string sql = "insert into hnkl(phase,result,asc_result,a1,a3,p3) values('{0}','{1}','{2}','{3}','{4}','{5}')";

            using (SQLiteConnection con = new SQLiteConnection(str))
            {
                using (SQLiteCommand cmd = con.CreateCommand())
                {
                    con.Open();
                    cmd.Transaction = con.BeginTransaction();

                    string result, asc, a1, a3, p3;
                    foreach (DataRow item in dt.Rows)
                    {
                        result = Convert.ToString(item[1]);
                        asc = Utils.Sort(result);
                        a1 = result.Substring(0, 2);
                        a3 = result.Substring(0, 8);
                        p3 = Utils.Sort(a3);

                        cmd.CommandText = String.Format(sql, item[0], result, asc, a1, a3, p3);
                        cmd.ExecuteNonQuery();
                    }

                    cmd.Transaction.Commit();

                }
            }

        }
    }
}
